CREATE TABLE `absentlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `employeeId` int(11) DEFAULT NULL,
  `studentId` int(11) DEFAULT NULL,
  `reason` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `absentlist_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employee` (`id`),
  CONSTRAINT `absentlist_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `classroom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `numberOfSits` int(11) NOT NULL,
  `numberOfStudents` int(11) NOT NULL,
  `area` varchar(255) NOT NULL,
  `deleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `state` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniClassroom` (`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=50025 DEFAULT CHARSET=utf8;



CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `description` varchar(20) NOT NULL,
  `format` varchar(20) NOT NULL,
  `belongTo` int(11) NOT NULL,
  `uploadDate` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `lastUpdateBy` int(11) NOT NULL,
  `locked` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(150) DEFAULT NULL,
  `lastname` varchar(150) NOT NULL,
  `street` varchar(150) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zipCode` varchar(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  `birthday` date NOT NULL,
  `email` varchar(100) NOT NULL,
  `cellPhone` varchar(30) NOT NULL,
  `workPhone` varchar(30) NOT NULL,
  `isPrincipalTeacherClassRoomId` int(11) DEFAULT NULL,
  `personToCallByEmergencyCase` varchar(50) DEFAULT NULL,
  `numberToCallByEmergencyCase` varchar(30) DEFAULT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `lastUpdate` date NOT NULL,
  `matriculationNumber` varchar(100) NOT NULL,
  `profession` varchar(100) NOT NULL,
  `placeOfBirth` varchar(45) NOT NULL,
  `firstLogin` date DEFAULT NULL,
  `lastLogin` date DEFAULT NULL,
  `createdby` varchar(45) DEFAULT NULL,
  `isOnline` bit(1) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `userPrivileg` varchar(45) DEFAULT NULL,
  `iban` varchar(50) DEFAULT NULL,
  `bic` varchar(45) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `maritalStatus` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniEmployee` (`firstname`,`lastname`,`birthday`),
  KEY `isPrincipalTeacherClassRoomId` (`isPrincipalTeacherClassRoomId`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`isPrincipalTeacherClassRoomId`) REFERENCES `classroom` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30018 DEFAULT CHARSET=utf8;



CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `start` varchar(20) NOT NULL,
  `finish` varchar(20) NOT NULL,
  `day` varchar(20) NOT NULL,
  `organisatorId` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE `image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `objectId` int(11) NOT NULL,
  `location` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110012 DEFAULT CHARSET=utf8;





CREATE TABLE `infrastructur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infrastructurCategoryId` int(11) NOT NULL,
  `instancePictureId` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `numberOfSits` int(11) NOT NULL,
  `deleted` bit(1) NOT NULL,
  `firstRegistration` int(11) NOT NULL,
  `lastUpdate` varchar(15) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `pictureId` int(11) DEFAULT NULL,
  `state` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `infrastructurCategoryId` (`infrastructurCategoryId`),
  CONSTRAINT `infrastructur_ibfk_1` FOREIGN KEY (`infrastructurCategoryId`) REFERENCES `infrastructurcategory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `infrastructurcategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70009 DEFAULT CHARSET=utf8;


CREATE TABLE `infrastructurmaterial` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infrastructurId` int(11) DEFAULT NULL,
  `materialId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `infrastructurId` (`infrastructurId`),
  KEY `materialId` (`materialId`),
  CONSTRAINT `infrastructurmaterial_ibfk_1` FOREIGN KEY (`infrastructurId`) REFERENCES `infrastructur` (`id`),
  CONSTRAINT `infrastructurmaterial_ibfk_2` FOREIGN KEY (`materialId`) REFERENCES `material` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `material` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `inUse` bit(1) NOT NULL,
  `deleted` bit(1) NOT NULL,
  `firstRegistration` int(11) NOT NULL,
  `lastUpdate` varchar(15) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `state` varchar(255) NOT NULL,
  `serialnumber` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `materialcategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classroomId` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `streetNr` varchar(10) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zipCode` varchar(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  `birthday` date NOT NULL,
  `email` varchar(100) NOT NULL,
  `cellPhone` varchar(30) NOT NULL,
  `personToCallByEmergencyCase` varchar(50) DEFAULT NULL,
  `numberToCallByEmergencyCase` varchar(30) DEFAULT NULL,
  `isDeleted` bit(1) NOT NULL,
  `firstRegistration` date NOT NULL,
  `matriculationNumber` varchar(100) NOT NULL,
  `placeOfBirth` varchar(100) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniStudent` (`firstname`,`lastname`,`birthday`),
  KEY `classroomId` (`classroomId`),
  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`classroomId`) REFERENCES `classroom` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `subjectprofessor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectId` int(11) NOT NULL,
  `professorId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `isDeleted` bit(1) DEFAULT NULL,
  `userPrivileg` varchar(15) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `cellPhone` varchar(30) DEFAULT NULL,
  `workPhone` varchar(30) DEFAULT NULL,
  `createdby` varchar(100) DEFAULT NULL,
  `firstLogin` date DEFAULT NULL,
  `lastLogin` date DEFAULT NULL,
  `isOnline` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniUser` (`username`,`password`),
  KEY `id` (`id`),
  KEY `id_2` (`id`),
  KEY `username` (`username`),
  KEY `password` (`password`)
) ENGINE=InnoDB AUTO_INCREMENT=100003 DEFAULT CHARSET=utf8;


CREATE TABLE `weeklyplan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectId` int(11) DEFAULT NULL,
  `start` varchar(20) NOT NULL,
  `finish` varchar(20) NOT NULL,
  `day` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subjectId` (`subjectId`),
  CONSTRAINT `weeklyplan_ibfk_1` FOREIGN KEY (`subjectId`) REFERENCES `subject` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;












ALTER TABLE absentlist AUTO_INCREMENT=40001;
ALTER TABLE classroom AUTO_INCREMENT=50001;
ALTER TABLE employee AUTO_INCREMENT=30001;
ALTER TABLE infrastructur AUTO_INCREMENT=60001;
ALTER TABLE infrastructurCategory AUTO_INCREMENT=70001;
ALTER TABLE instancepicture AUTO_INCREMENT=10001;
ALTER TABLE student  AUTO_INCREMENT= 20001;
ALTER TABLE subject AUTO_INCREMENT=90001;
ALTER TABLE user AUTO_INCREMENT=100001;
ALTER TABLE weeklyplan AUTO_INCREMENT=80001;
ALTER TABLE material AUTO_INCREMENT=101001;
ALTER TABLE event AUTO_INCREMENT=102001;
ALTER TABLE infrastructurMaterial AUTO_INCREMENT=103001;
ALTER TABLE materialCategory AUTO_INCREMENT=104001;
ALTER TABLE document AUTO_INCREMENT=130001;
ALTER TABLE materialcategory AUTO_INCREMENT=110001;
ALTER TABLE subjectprofessor AUTO_INCREMENT=120001;

Indexer:

ALTER TABLE user ADD INDEX (username);
ALTER TABLE user ADD INDEX (username);
ALTER TABLE user ADD INDEX (password);


